import pytz
import logging
import xlwt
import math
from pytz import timezone, utc
from datetime import datetime, timedelta, time
from itertools import groupby

from odoo import http, _
from odoo.http import request, content_disposition
from odoo.tools import DEFAULT_SERVER_DATE_FORMAT, DEFAULT_SERVER_DATETIME_FORMAT

_logger = logging.getLogger(__name__)


class Controller(http.Controller):

    @http.route('/download/mrp/workorder/wip/stocktaking/report/excel', type='http', auth='user', website=True)
    def download_mrp_workorder_wip_stocktaking_report_excel(self, **data):
        data = request.env['mrp.workorder.sum'].search([('production_id', '!=', False)])
        book = xlwt.Workbook(encoding='utf-8')

        def create_style(font_height, center=True, borders=True):
            style = xlwt.XFStyle()
            font = xlwt.Font()
            font.height = font_height
            style.font = font
            if center:
                # 垂直居中 水平居中
                vertically_horizontally_centered = xlwt.Alignment()
                vertically_horizontally_centered.vert = xlwt.Alignment.VERT_CENTER
                vertically_horizontally_centered.horz = xlwt.Alignment.HORZ_CENTER
                style.alignment = vertically_horizontally_centered
            if borders:
                border = xlwt.Borders()
                border.bottom = border.top = border.left = border.right = xlwt.Borders.THIN
                style.borders = border
            return style

        center_field_style = create_style(15 * 15, center=True)
        field_style = create_style(15 * 15, center=False)

        for station, g in groupby(data.sorted(key=lambda x: x.work_station_id.id), key=lambda x: x.work_station_id):
            sheet = book.add_sheet(station.name)
            records = request.env['mrp.workorder.sum'].concat(*g)
            sheet_data = [self._get_label_list()]
            for rec in records:
                if not rec.production_id:
                    continue
                sheet_data.append([
                    rec.work_station_id.name,
                    rec.production_id.name,
                    rec.product_id.name,
                    rec.value_pnl,
                    rec.value_set,
                    rec.qty_wip or 0,
                    rec.qty_wip_set or 0,
                    rec.qty_wip_pnl or 0,
                    rec.outbound_qty_wip_pnl or 0
                ])

            for i, item in enumerate(sheet_data):
                sheet.row(i).height_mismatch = True
                sheet.row(i).height = 300
                for index, v in enumerate(item):
                    sheet.write(i, index, v, center_field_style)

            sheet.col(0).width = 256 * 10
            sheet.col(1).width = 256 * 30
            sheet.col(2).width = 256 * 20
            sheet.col(3).width = 256 * 13
            sheet.col(4).width = 256 * 13
            sheet.col(5).width = 256 * 13
            sheet.col(6).width = 256 * 13
            sheet.col(7).width = 256 * 13
            sheet.col(8).width = 256 * 13

        for location in request.env['stock.location'].search([('location_id.barcode', '=', 'WH-PREPRODUCTION')]):
            sheet = book.add_sheet(location.name)
            request.env.cr.execute(f"""
                select pt.name, pp.default_code, s.name, stock_quant.quantity, uu.name->'zh_CN', sl.name
                    from stock_quant
                             left join product_product pp on stock_quant.product_id = pp.id
                             left join product_template pt on pp.product_tmpl_id = pt.id
                             left join stock_lot sl on stock_quant.lot_id = sl.id
                             left join stock_location s on stock_quant.location_id = s.id
                             left join uom_uom uu on pt.uom_id = uu.id
                    where stock_quant.location_id = {location.id};
            
            """)
            result = request.env.cr.fetchall()
            field_name_list = ['物料名称', '物料编码', '位置', '数量', '单位', '批次号']
            for index, name in enumerate(field_name_list):
                sheet.write(0, index, name, field_style)
            i = 1
            for res in result:
                for index, r in enumerate(res):
                    if index == 0:
                        sheet.write(i, index, r, field_style)
                    else:
                        sheet.write(i, index, r, center_field_style)
                i += 1

            sheet.col(0).width = 256 * 60
            sheet.col(1).width = 256 * 50

        user_tz = request.env.user.tz or 'UTC'
        timezone = pytz.timezone(user_tz)
        utc_time = pytz.utc.localize(datetime.now())
        local_time = utc_time.astimezone(timezone)
        file_name = f'盘点WIP报表-{local_time.strftime(DEFAULT_SERVER_DATE_FORMAT)}.xls'
        response = request.make_response(None, headers=[
            ('Content-Type', 'application/vnd.ms-excel'),
            ('Content-Disposition', content_disposition(file_name))
        ])
        book.save(response.stream)
        return response

    def _get_label_list(self):
        return [
            '工站', '生产订单', '产品', 'PCS/PNL', 'PCS/SET', 'WIP(PCS)', 'WIP(SET)', 'WIP(PNL)', '外发(PNL)',
        ]

    @http.route('/download/station/use/report/excel/<string:business_data>', type='http', auth='user', website=True)
    def download_station_use_report_report_excel(self, business_data=None, **data):
        start_date, end_date, station_id = business_data.split(',')
        records = request.env['s.station.use.report'].with_context({
            'start_date': start_date,
            'end_date': end_date,
            'station_id': station_id
        }).search([])

        header = [
            '单号', '业务类型', '工站', '部门', '领用人', '领用时间', '源位置', '目的位置', '源单据', '产品', '产品编码',
            '数量', '单位', '总金额', '币种'
        ]
        book = xlwt.Workbook(encoding='utf-8')
        sheet = book.add_sheet('领用出库')
        sheet2 = book.add_sheet('生产消耗')

        def create_style(font_height, center=True, borders=True):
            style = xlwt.XFStyle()
            font = xlwt.Font()
            font.height = font_height
            style.font = font
            if center:
                # 垂直居中 水平居中
                vertically_horizontally_centered = xlwt.Alignment()
                vertically_horizontally_centered.vert = xlwt.Alignment.VERT_CENTER
                vertically_horizontally_centered.horz = xlwt.Alignment.HORZ_CENTER
                style.alignment = vertically_horizontally_centered
            if borders:
                border = xlwt.Borders()
                border.bottom = border.top = border.left = border.right = xlwt.Borders.THIN
                style.borders = border
            return style

        field_style = create_style(15 * 15, center=True)
        for i, item in enumerate(header):
            sheet.write(0, i, item, field_style)
            sheet2.write(0, i, item, field_style)
        user_tz = timezone(request.env.user.tz or 'UTC')
        index = 1
        for rec in records.filtered(lambda x: x.business_type in ('领用出库', '领用退回')):
            done_date = utc.localize(rec.use_date).astimezone(user_tz)
            sheet.write(index, 0, rec.picking_id.name or '', field_style)
            sheet.write(index, 1, rec.business_type or '', field_style)
            sheet.write(index, 2, rec.station_id.name or '', field_style)
            sheet.write(index, 3, rec.department_id.name or '', field_style)
            sheet.write(index, 4, rec.user_id.name or '', field_style)
            sheet.write(index, 5, done_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT) or '' or '', field_style)
            sheet.write(index, 6, rec.location_id.name, field_style)
            sheet.write(index, 7, rec.location_dest_id.name or '', field_style)
            sheet.write(index, 8, rec.origin or '', field_style)
            sheet.write(index, 9, rec.product_id.name or '', field_style)
            sheet.write(index, 10, rec.product_id.default_code or '', field_style)
            sheet.write(index, 11, rec.qty or '', field_style)
            sheet.write(index, 12, rec.uom_id.name or '', field_style)
            sheet.write(index, 13, round(rec.amount, 3) or '', field_style)
            sheet.write(index, 14, 'THB', field_style)
            index += 1

        index = 1
        for rec in records.filtered(lambda x: x.business_type in ('生产消耗', '生产消耗退回')):
            done_date = utc.localize(rec.use_date).astimezone(user_tz)
            sheet2.write(index, 0, rec.picking_id.name or '', field_style)
            sheet2.write(index, 1, rec.business_type or '', field_style)
            sheet2.write(index, 2, rec.station_id.name or '', field_style)
            sheet2.write(index, 3, rec.department_id.name or '', field_style)
            sheet2.write(index, 4, rec.user_id.name or '', field_style)
            sheet2.write(index, 5, done_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT) or '' or '', field_style)
            sheet2.write(index, 6, rec.location_id.name, field_style)
            sheet2.write(index, 7, rec.location_dest_id.name or '', field_style)
            sheet2.write(index, 8, rec.origin or '', field_style)
            sheet2.write(index, 9, rec.product_id.name or '', field_style)
            sheet2.write(index, 10, rec.product_id.default_code or '', field_style)
            sheet2.write(index, 11, rec.qty or '', field_style)
            sheet2.write(index, 12, rec.uom_id.name or '', field_style)
            sheet2.write(index, 13, round(rec.amount, 3) or '', field_style)
            sheet2.write(index, 14, 'THB', field_style)
            index += 1

        file_name = f'物料领用及生产消耗.xls'
        response = request.make_response(None, headers=[
            ('Content-Type', 'application/vnd.ms-excel'),
            ('Content-Disposition', content_disposition(file_name))
        ])
        book.save(response.stream)
        return response
